/**
 * Project: ld.biz.service
 * 
 * File Created at 2012-9-28
 * $Id$
 * 
 * Copyright 1999-2100 Alibaba.com Corporation Limited.
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of
 * Alibaba Company. ("Confidential Information").  You shall not
 * disclose such Confidential Information and shall use it only in
 * accordance with the terms of the license agreement you entered into
 * with Alibaba.com.
 */
package com.liangdev.biz.dao.common.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.liangdev.biz.dao.autocode.DbInfoDao;
import com.liangdev.biz.dao.common.DbDataQueryDao;
import com.liangdev.biz.dataobject.autocode.DbInfoDO;
import com.liangdev.biz.entity.autocode.FieldMetaData;

/**
 * TODO Comment of DbDataQueryDaoImpl
 * 
 * @author chongan.wangca
 */
public class DbDataQueryDaoImpl implements DbDataQueryDao {

    private Logger    logger = LoggerFactory.getLogger(DbDataQueryDaoImpl.class);

    private DbInfoDao dbInfoDao;

    /*
     * (non-Javadoc)
     * @see com.liangdev.biz.dao.common.DbDataQueryDao#queryData(java.lang.Long,
     * java.lang.String, java.lang.String)
     */
    @Override
    public List<List<FieldMetaData>> queryData(Long dbInfoId, String tableName, String sql)
            throws Exception {

        DbInfoDO dbInfoDO = dbInfoDao.findById(dbInfoId);
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection connection = null;
        try {
            connection = getConnection(dbInfoDO.getDbType(), dbInfoDO.getIp(), dbInfoDO.getPort(),
                    dbInfoDO.getDbName(), dbInfoDO.getUser(), dbInfoDO.getPassword());

            pstmt = (PreparedStatement) connection.prepareStatement(getSqlPage(
                    dbInfoDO.getDbType(), sql));
            //                pstmt.setString(1, tableName);
            //                //这点特别要注意:如果是Oracle.而对于mysql可以不用加
            pstmt.execute();

            List<List<FieldMetaData>> dataList = new ArrayList<List<FieldMetaData>>();

            //存放表结构信息
            List<FieldMetaData> fieldMetaDataList = new ArrayList<FieldMetaData>();
            for (int i = 1; i <= pstmt.getMetaData().getColumnCount(); i++) {
                FieldMetaData fieldMetaData = new FieldMetaData();
                fieldMetaData.setDbFiledName(pstmt.getMetaData().getColumnName(i));
                fieldMetaData.setDbFieldType(pstmt.getMetaData().getColumnTypeName(i));
                fieldMetaDataList.add(fieldMetaData);
            }

            //第0个位置的List是存放表结构信息，不含数据
            dataList.add(fieldMetaDataList);

            //处理表记录数
            rs = pstmt.getResultSet();

            //如果没有记录数，则直接返回表结构信息即可
            if (rs == null) {
                return dataList;
            }

            while (rs.next()) {
                List<FieldMetaData> fieldsList = new ArrayList<FieldMetaData>();
                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    FieldMetaData fieldMetaData = new FieldMetaData();
                    fieldMetaData.setDbFiledName(rs.getMetaData().getColumnName(i));
                    fieldMetaData.setDbFieldType(rs.getMetaData().getColumnTypeName(i));
                    fieldMetaData.setValue(rs.getObject(i));
                    fieldsList.add(fieldMetaData);
                }
                dataList.add(fieldsList);
            }
            return dataList;

        } finally {
            if (rs != null) {
                rs.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }

    private String getSqlPage(String dbType, String sql) {
        if ("ORACLE".equals(dbType)) {
            return "SELECT T.* FROM ( " + sql.toUpperCase() + " ) T WHERE ROWNUM <= 200";
        } else if ("MYSQL".equals(dbType)) {
            return "SELECT T.* FROM ( " + sql.toUpperCase() + " ) T LIMIT 200";
        }
        return null;
    }

    /**
     * @param ip
     * @param port
     * @param dbName
     * @param user
     * @param password
     * @return
     * @throws Exception
     */
    private Connection getConnection(String dbType, String ip, String port, String dbName,
                                     String user, String password) throws Exception {

        Connection connection = null;
        if ("ORACLE".equals(dbType)) {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            connection = DriverManager.getConnection(
                    "jdbc:oracle:thin:@" + ip.trim() + ":" + port.trim() + ":" + dbName, user,
                    password);
        } else if ("MYSQL".equals(dbType)) {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://" + ip.trim() + ":" + port.trim() + "/" + dbName, user, password);
        }
        return connection;
    }

    public void setDbInfoDao(DbInfoDao dbInfoDao) {
        this.dbInfoDao = dbInfoDao;
    }

    public static void main(String[] args) throws Exception {
        DbDataQueryDao dbDataQueryDao = new DbDataQueryDaoImpl();

        //        String outerTradeId = "92157280259208"; //92157280249208 //92157280259208
        //        String fieldName = "outer_order_id";
        //        String tableName = "oc_order";
        //        String endStr = "1";

        String outerTradeId = "92157280239208";
        String fieldName = "outer_trade_id";
        String tableName = "oc_trade";
        String endStr = "0";

        List<List<FieldMetaData>> sourceList = dbDataQueryDao.queryData(1L, "oc_trade",
                "select * from " + tableName + " where " + fieldName + " = '" + outerTradeId + "'");
        List<List<FieldMetaData>> targetList = dbDataQueryDao.queryData(1L, "oc_trade",
                "select * from " + tableName + " where " + fieldName + " = '" + outerTradeId
                        + endStr + "'");

        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < sourceList.get(1).size(); i++) {
            String sourceValue = sourceList.get(1).get(i).getValueStr();
            String targetValue = targetList.get(1).get(i).getValueStr();
            if (!sourceValue.equals(targetValue)) {
                sb.append("字段名：" + sourceList.get(1).get(i).getDbFieldNameLC() + " | " + "TOP值："
                        + sourceValue + " | " + "Notify值：" + targetValue + "\n");
            }
        }
        System.out.println(sb.toString());
    }

}
